#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author   : PR
# @Time     : 2023/1/18 19:51
# @File     : openMdb.py
# @Project  : pythonoffice

import pypyodbc
import pandas as pd
import os
import time
from sqlalchemy import create_engine


def readMdb(p_path, tableNamePoint, tableNameLine, pointColumnName, lineColumnName, resultColumnName):
    '''
    操作一个表
    :param p_path:
    :param tableNamePoint:
    :param tableNameLine:
    :return:
    '''
    # p_path = r'C:\Users\kau2a2122900r01sk-1.mdb'
    connStr = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + p_path + ';PWD=007'
    conn = pypyodbc.win_connect_mdb(connStr)  # 链接数据库
    # cur = conn.cursor()  # 创建游标
    # singleTableOperation(mdbpath,cur, tableNamePoint,pointColumnName,resultColumnName)
    # singleTableOperation(mdbpath,cur, tableNameLine,lineColumnName,resultColumnName)
    singleTableOperation(p_path, conn, tableNamePoint, pointColumnName, resultColumnName)
    singleTableOperation(p_path, conn, tableNameLine, lineColumnName, resultColumnName)
    # print("****************Begin to process\"表:ActualValues_T\"****************")
    # print("\"表:%s\"总行数 = %d" %('ActualValues_T', total_rows))
    # print("\"表:%s\"总列数 = %d" %('ActualValues_T', total_cols))
    # print(type(alldata))
    conn.close()  # 关闭数据库


def singleTableOperation(mdbpath, cur, tableNamePoint, pointColumnName, resultColumnName):
    '''
    单表提取操作
    :param cur:
    :param tableNamePoint:
    :return:
    '''
    # sql = "SELECT * FROM " + 'ActualValues_T'  # 取表 ActualValues_T
    sql = "SELECT * FROM " + tableNamePoint  # 取表 ActualValues_T
    # cur.execute(sql)
    # alldata = cur.fetchall()  # 取 ActualValues_T 所有数据
    # total_rows = len(alldata)
    # total_cols = len(alldata[0])
    # df1 = pd.DataFrame(alldata)  # 这时候是以行为标准写入的
    df1 = pd.read_sql(sql, cur)
    # print("hahahahhhhhhhhhh",list(df1))
    # print("2-hahahahhhhhhhhhh",df1.keys())
    # print("3-hahahahhhhhhhhhh",df1.columns)
    # print(df1.head())
    # df1.insert(loc=len(df1.columns), column='player', value=player_vals)
    df1[resultColumnName] = df1[pointColumnName].apply(lambda x: x[0:4])
    # df1[resultColumnName] = df1[pointColumnName][0:4]
    exportExcel(mdbpath, df1, tableNamePoint)
    # print(df1)


def exportExcel(open_path, df, tableNamePoint):
    filename = os.path.basename(open_path)
    # print(os.path.basename(path))

    stem, suffix = os.path.splitext(filename)
    # print(stem)  # test
    # print(suffix)  # .py
    exportPath = os.path.split(os.path.abspath(open_path))[0]
    filename = os.path.split(os.path.abspath(open_path))[1]
    now_time = time.strftime('%Y-%m-%d-%H-%M-%S', time.localtime())
    # outname = "./"+stem+"更新excel列" + now_time + ".xlsx"
    outname = "./" + stem + now_time + tableNamePoint + "_提取excel.xlsx"
    # print(outname)
    outputPath = exportPath + outname
    # outputPath = exportPath +'./excel转txt1.txt'
    # df.to_excel(outputPath, sep='\t', index=None)
    df.to_excel(outputPath, index=False, encoding="utf-8")
    print('导出excel完成')


def file_name(file_path):
    """
    获取每个文件的路径
    root 为当前目录路径
    dirs 为当前路径下所有子目录
    files 为当前路径下所有非目录子文件
    :param file_path:
    :return:
    """
    paths_list = []
    for root, dirs, files in os.walk(file_path):
        for file in files:
            path_list = os.path.join(root, file)
            paths_list.append(path_list)
    return paths_list


# def loop_func(func, second):
#     # 每隔second秒执行func函数
#     while True:
#         func()
#         time.sleep(second)
#     for path in listMdb:
#         try:
#             # print(path)
#             readMdb(path, tableNamePoint, tableNameLine, pointColumnName, lineColumnName, resultColumnName)
#         except Exception as e:
#             print("读取错误:", e)


if __name__ == '__main__':
    mdbpath1 = "E:\\data\\2.青羊管网\\青羊管网成果\\三期数据库\\0043草市街街道凤栖桐数据库.mdb"
    mdbpath = r"E:\data\2.青羊管网\青羊管网成果\三期数据库"
    # print(file_name(mdbpath))

    listMdb = file_name(mdbpath)
    tableNamePoint = "管点调查表"
    tableNameLine = "管线调查表"

    # pointColumnName="exp_no"
    # lineColumnName="s_point"
    pointColumnName = 'exp_no'
    lineColumnName = 's_point'

    resultColumnName = "DKID"

    # readMdb(mdbpath1,tableNamePoint,tableNameLine,pointColumnName,lineColumnName,resultColumnName)

    for path in listMdb:
        try:
            # print(path)
            readMdb(path, tableNamePoint, tableNameLine, pointColumnName, lineColumnName, resultColumnName)
            time.sleep(2)
        except Exception as e:
            print("读取错误:", e)

    # readMdb(mdbpath,tableNamePoint,tableNameLine,pointColumnName,lineColumnName,resultColumnName)
